﻿CREATE FUNCTION dbo.udf_CharlistToTable
	(
	@text      text,
	@delimiter char(1) = ','
	)
returns @returntable table(subcolstr varchar(200))       

AS
	BEGIN
	
	declare @fieldlen bigint, @nbegin bigint, @nsubstrlen bigint, @nflag bigint, @flag int
	declare @sourcestring varchar(8000), @commastr varchar(8000), @splitstr varchar(8000), @splitlen int, @length int
	declare @branch_type varchar(10), @agent_branch_id varchar(50), @count bigint
	
	select @fieldlen =0, @nbegin=0, @nsubstrlen=0, @nflag = 0, @flag=0
	select @fieldlen = datalength(@text)
	
	if @fieldlen >7000
         begin
                 while @nbegin+1<=@fieldlen
                 begin
                 --------------------------------------------------------------------------------------------------
                         if @fieldlen-@nbegin>7000 
                         begin
                         --------------------------------------------------------------------------------
                                 set @nsubstrlen=7000
                                 set @flag = 1
                         --------------------------------------------------------------------------------
                         end
                         else
                         begin
                         --------------------------------------------------------------------------------
                                 set @nsubstrlen = @fieldlen - @nbegin    
                                 set @flag =0
                         --------------------------------------------------------------------------------
                         end
                         select   @sourcestring=substring(@text,@nbegin+1,@nsubstrlen) 
        
                         select   @splitstr = @sourcestring,
                                 @splitlen = charindex(',',@splitstr),
                                 @length    = datalength(@splitstr),
                                 @nflag = @splitlen
                                
                        
                         while @length>0
                         begin
                         ----------------------------------------------------------------------------------
                          if      @splitlen=0 
                                 begin
                                 --------------------------------------------------------------------------
                                         if @length = 7000
                                         begin
                                         ------------------------------------------------------------------
                                                 delete @returntable
                                                 insert @returntable (subcolstr)   values(null)
                                                 goto end_label
                                         ------------------------------------------------------------------
                                         end
                                         if @flag=0
                                             set @nbegin = @nbegin + @length
                                         break
                                 --------------------------------------------------------------------------
                                 end
                                         set @nbegin = @nbegin + @nflag

                                  set     @commastr =@splitstr
                                         if @commastr <> ''
                                         insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
                                  select @splitstr = substring(@commastr,@splitlen+1,@length),
                                                @length    = datalength(@splitstr),
                                                @splitlen = charindex(',',@splitstr)
                                         set     @nflag = @splitlen
                         -------------------------------------------------------------------------------------
                         end
                 --------------------------------------------------------------------------------------------------------------
                 end                 
                 set @splitlen=charindex(',',@splitstr)  
                 if      @splitlen=0 
                        set @splitlen=datalength(@splitstr)                

                 if @splitstr<>''
                  insert @returntable (subcolstr) values(substring(@splitstr,1,@splitlen))
         end
/*---------------------------------------------------------------------
         如果小于7000 
                 则再判断是否为NULL值        
                         如果为NULL则在数据库中取相应的数据
                         否则放入到VARCHAR中进行分隔
----------------------------------------------------------------------*/   
         else
         begin                  
                select   @sourcestring=substring(@text,1,@fieldlen) 
                 -----------------------------------------------------------------------------------------------------
                 select   @splitstr=@sourcestring,
                  @splitlen=charindex(',',@splitstr),
                  @length=datalength(@splitstr)

          while @length>0
          begin
          --------------------------------------------------------------------------------------
           if      @splitlen=0 
                         set @splitlen=@length+1
         
           set     @commastr =@splitstr
           insert @returntable (subcolstr) values(substring(@commastr,1,@splitlen-1))
           select @splitstr = substring(@commastr,@splitlen+1,@length),
                         @length=datalength(@splitstr),
                         @splitlen=charindex(',',@splitstr)
                 --------------------------------------------------------------------------------------
                 end
         end        
         --------------------------------------------------------------------------------------------------------------
         end_label:    
         return 


	--DECLARE @pos      int,
		--@textpos  int,
		--@chunklen smallint,
		--@tmpstr   varchar(MAX),
		--@leftover varchar(MAX),
		--@tmpval   varchar(MAX)

	--SET @textpos = 1
	--SET @leftover = ''
	--WHILE @textpos <= datalength(@list) / 2

	--BEGIN
		--SET @chunklen = 8000 - datalength(@leftover) / 2
		--SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
		--SET @textpos = @textpos + @chunklen
		--SET @pos = charindex(@delimiter, @tmpstr)

		--WHILE @pos > 0
		--BEGIN
			--SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
			--INSERT @tbl ([str]) VALUES(@tmpval)
			--SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
			--SET @pos = charindex(@delimiter, @tmpstr)
		--END

		--SET @leftover = @tmpstr
	--END

	--INSERT @tbl([str]) VALUES (ltrim(rtrim(@leftover)))
	END


